//
// C++ Implementation: activitydbinterface
//
// Description: This class provides the interface to the database
// that stores activity information.  It also composes the vector
// of activities, which is used by most widgets/classes.
//
//
// Author: Willi Ballenthin <wilbal1087@gmail.com>, (C) 2008
//
// Copyright: See COPYING file that comes with this distribution
//
//
#include "activitydbinterface.h"
#include <QDebug>

/**
* Constructor.
* @param db The database on which queries will be executed.
* @param parent The parent object.
*/
ActivityDbInterface::ActivityDbInterface ( QSqlDatabase* db, QObject *parent )
        : QObject ( parent )
{
    stats = new ActivityStatistics;

    sql = new QSqlQuery(*db);

    // this is where the data structure of activities is initialized
    // for the entire application.
    vector = new QVector<Activity>;
}


/**
* Sets the query that will be executed on the database.
* The results can be limited by providing part of the SQL query.
*
* The filter should be of the form:
* WHERE var='filter', var2='filter2' ...
* @param filter The fragment of SQL syntax to filter the table through.
*/
void ActivityDbInterface::setFilter ( QString filter )
{

    statement  = "SELECT date, time, duration, distance, effort, type, location, route, notes, splits, id " ;
    statement += QString ( " FROM logTable " );
    statement += filter;
    statement += QString ( " ORDER BY date " );
}

QString ActivityDbInterface::getFilter()
{
    return statement;
}

/**
* An overloaded function that returns a full SQL statement based on a filter.
* @param filter The fragment of SQL syntax the filter the table through.
* @return A full SQL statement.
*/
QString ActivityDbInterface::getFilter ( QString filter )
{
    QString query ( "SELECT date, time, duration, distance, effort, type, location, route, notes, splits, id " );
    query += QString ( " FROM logTable " );
    query += filter;
    query += QString ( " ORDER BY date " );
    return query;
}

/**
* Returns the result of an SQL query as a vector of activities,
* which is the standard data structure.
* @return A vector of Activities with zero elements if the query failed.
*/
// TODO: returning old vector when theres an error may not be the
// correct approach. How does another object know something went
// wrong?
QVector<Activity>* ActivityDbInterface::query()
{
    if ( !sql->exec ( statement ) )
    {
        qWarning ( "Query to database failed" );
        qDebug() << sql->lastError();
        return vector;
    }

    if ( !sql->isActive() || !sql->isSelect() )
    {
        qWarning ( "Record is not active, or not a SELECT result" );
        return vector;
    }

    if(sql->size() == 0)
    {
        qWarning ( "Specified filter returns no results" );
        return vector;
    }
    Activity act ( this );
    vector->clear();
    if ( sql->first() )
    {
        do
        {
            act.setDate ( sql->value ( 0 ).toDate() );
            act.setTime ( sql->value ( 1 ).toTime() );
            act.setDuration ( sql->value ( 2 ).toTime() );
            act.setDistance ( sql->value ( 3 ).toDouble() );
            act.setEffort ( sql->value ( 4 ).toInt() );
            act.setType ( sql->value ( 5 ).toString() );
            act.setLocation ( sql->value ( 6 ).toString() );
            act.setRoute ( sql->value ( 7 ).toString() );
            act.setNotes ( sql->value ( 8 ).toString() );
            act.setSplits ( sql->value ( 9 ).toString() );
            act.setId ( sql->value ( 10 ).toInt() );
            vector->append ( act );
        }
        while ( sql->next() );
    }
    emit updatedModel();

    return vector;
}

/**
* An overloaded function that allows arbitrary queries to be performed on the database
* returned in a Activity structure.
* @param statement The SQL query to execute.
* @param vector The Activity data structure to which to save the data.
* @return A reference to the data structure passed as parameter two, now updated.
*/
QVector<Activity>* ActivityDbInterface::query ( QString statement,
                                                QVector<Activity>* vector )
{
    if ( !sql->exec ( statement ) )
    {
        qWarning ( "Query to database failed" );
        qDebug() << sql->lastError();
        return vector;
    }

    if ( !sql->isActive() || !sql->isSelect() )
    {
        qWarning ( "Record is not active, or not a SELECT result" );
        return vector;
    }

    Activity act ( this );
    vector->clear();
    if ( sql->first() )
    {
        do
        {
            act.setDate ( sql->value ( 0 ).toDate() );
            act.setTime ( sql->value ( 1 ).toTime() );
            act.setDuration ( sql->value ( 2 ).toTime() );
            act.setDistance ( sql->value ( 3 ).toDouble() );
            act.setEffort ( sql->value ( 4 ).toInt() );
            act.setType ( sql->value ( 5 ).toString() );
            act.setLocation ( sql->value ( 6 ).toString() );
            act.setRoute ( sql->value ( 7 ).toString() );
            act.setNotes ( sql->value ( 8 ).toString() );
            act.setSplits ( sql->value ( 9 ).toString() );
            act.setId ( sql->value ( 10 ).toInt() );
            vector->append ( act );
        }
        while ( sql->next() );
    }
    else
    {
        qWarning() << "model is empty";
    }
    return vector;
}


/**
* HACK:
* This method does not fit in with the use of the class. Migrate this
* functionality elsewhere.
* Queries database and calculates relevant statistics about activities.
* TODO: do this generally, not specificially for running.
* TODO: error checking queries.
* @return a reference to an ActivityStatistics object containing the updated info.
*/
ActivityStatistics* ActivityDbInterface::getStatistics()
{
    //
    //  number of activities total
    //
    if ( !sql->exec ( "SELECT date FROM logTable WHERE type='Run'" ) )
    {
        qWarning() << "query to database failed";
    };
    stats->numRuns = sql->size();


    if ( !sql->exec ( "SELECT date FROM logTable WHERE type='Workout'" ) )
    {
        qWarning() << "query to database failed";
    };
    stats->numWorkouts = sql->size();

    if ( !sql->exec ( "SELECT date FROM logTable WHERE type='Race'" ) )
    {
        qWarning() << "query to database failed";
    };
    stats->numRaces = sql->size();

    if ( !sql->exec ( "SELECT date FROM logTable WHERE type='Run' OR type='Workout' "
                 "OR type='race' ORDER BY date ASC" ) )
    {
        qWarning() << "query to database failed";
    };
    stats->numActivities = sql->size();
    sql->first();
    QDate dateBegin = sql->value ( 0 ).toDate();
    stats->daysTotal= dateBegin.daysTo ( QDate::currentDate() );

    //
    //  days with specific activity
    //
    if ( !sql->exec ( "SELECT DISTINCT date FROM logTable WHERE type='Run'" ) )
    {
        qWarning() << "query to database failed";
    };
    stats->daysWithRun = sql->size();

    if ( !sql->exec ( "SELECT DISTINCT date FROM logTable WHERE type='Workout'" ) )
    {
        qWarning() << "query to database failed";
    };
    stats->daysWithWorkout = sql->size();

    if ( !sql->exec ( "SELECT DISTINCT date FROM logTable WHERE type='Race'" ) )
    {
        qWarning() << "query to database failed";
    };
    stats->daysWithRace = sql->size();

    stats->daysWithActivity = stats->daysWithRun + stats->daysWithWorkout + stats->daysWithRace;



    //
    //  mileage
    //
    if ( !sql->exec ( "SELECT SUM(distance) FROM logTable WHERE "
                 "type='Run' OR type='Workout' OR type='Race'" ) )
    {
        qWarning() << "query to database failed";
    };
    sql->first();
    stats->unitsTotal = sql->value ( 0 ).toDouble();

    QString unitsPerWeekQuery ( "SELECT SUM(distance) FROM logTable WHERE "
                                "(type='Run' OR type='Workout' OR type='Race') AND date>'" );
    unitsPerWeekQuery += QDate::currentDate().addDays ( -7 ).toString ( "yyyy-MM-dd" );
    unitsPerWeekQuery += QString ( "'" );
    if ( !sql->exec ( unitsPerWeekQuery ) )
    {
        qWarning() << "query to database failed";
    };
    sql->first();
    stats->unitsPerWeek = sql->value ( 0 ).toDouble();

    stats->unitsAvgPerDay = stats->unitsTotal /  stats->daysTotal;
    stats->unitsAvgPerWeek = stats->unitsAvgPerDay * 7;


    //
    //  time per week
    //
    if ( !sql->exec ( "SELECT SUM(duration) FROM logTable WHERE type='Run' OR type='Workout' OR type='Race'" ) )
    {
        qWarning() << "query to database failed";
    };
    sql->first();
    stats->timeTotal = sql->value ( 0 ).toTime();

    QString timePerWeekQuery ( "SELECT SUM(duration) FROM logTable WHERE (type='Run' OR type='Workout' OR type='Race') AND date>'" );
    timePerWeekQuery += QDate::currentDate().addDays ( -7 ).toString ( "yyyy-MM-dd" );
    timePerWeekQuery += QString ( "'" );
    if ( !sql->exec ( timePerWeekQuery ) )
    {
        qWarning() << "query to database failed";
    };
    sql->first();
    stats->timePerWeek = sql->value ( 0 ).toTime();

    // TODO: get avg time working. date operations needed
    // stats->timeAvgPerDay = stats->timeTotal /  stats->daysTotal;
    // stats->timeAvgPerWeek = stats->timeAvgPerDay * 7;

    return stats;
}


/**
* Returns the 'model', which is a vector of activities.
* @return A reference to the vector of activities returned by the most recent query.
*/
QVector<Activity>* ActivityDbInterface::getModel()
{
    return vector;
}

